package com.easyj.base.common.imp;


import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.xml.stream.XMLEventReader;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.events.Characters;
import javax.xml.stream.events.EndElement;
import javax.xml.stream.events.StartElement;
import javax.xml.stream.events.XMLEvent;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import com.easyj.base.exception.ImpValidException;
import com.easyj.base.util.DateTimeUtil;





/**
 * 导入Excel 工具类
 * @author hjf
 *
 */
public class ImpUtils {
	public static final int XLS_MAX_ROW = 65535; //0开始
	private static final String FIELD_TAG = "field";
	private static final String DESC_TAG = "desc";
	private ImpUtils(){
		
	}
	
	/**
	 * 读取导入定义XML
	 * @param filename
	 * @return
	 * @throws Exception
	 */
	public static ImpDefine getImpDefine(String filename) throws Exception {
	      Resource resource = new ClassPathResource("\\impTemplates\\"+filename);
	      if (!resource.isReadable()) {
	    	  throw new Exception("导入模板文件不可读取！");
	      }
	        
	      ImpDefine rtn=new ImpDefine();
	      List<ImpField> fields=new ArrayList<ImpField>();
	      InputStream is=null;
	      try{
		      is = resource.getInputStream();
		      XMLInputFactory inputFactory = XMLInputFactory.newInstance();
			  inputFactory.setProperty("javax.xml.stream.isCoalescing", true); //否则读取多行数据时出问题
			  XMLEventReader eventReader = inputFactory.createXMLEventReader(is);
			  ImpField curField=null;
			  String   curTag="";
			  boolean  dealingField=false;
			  while (eventReader.hasNext()) {
					XMLEvent event = eventReader.nextEvent();
					
					if (event.isStartElement()) {
						StartElement em=event.asStartElement();
						curTag=em.getName().getLocalPart();  //Tag Name
						if(curTag.equals(FIELD_TAG)){
							curField =new ImpField();
							dealingField=true;
						}
					
					}
					else if (event.isEndElement()) {
						EndElement em=event.asEndElement();
						String tag=em.getName().getLocalPart();
						if(tag.equals(FIELD_TAG)){
							dealingField=false;
							if(curField!=null){
								if(StringUtils.isBlank(curField.getName())||StringUtils.isBlank(curField.getTitle())||StringUtils.isBlank(curField.getType())){
									throw new Exception("导入模板文件的导入字段定义不完整！");
								}
							    fields.add(curField);
							}
						}
					}
					else if (event.isCharacters()) {
						Characters cs= event.asCharacters();
						if(!cs.isWhiteSpace()){
							String str=cs.getData().trim();//trim
							if(dealingField){
								if(curTag.equals("name")){
									curField.setName(str);
								}else if(curTag.equals("title")){
									curField.setTitle(str);
								}else if(curTag.equals("type")){
									curField.setType(str);
								}
								else if(curTag.equals("required")){
									if(str.equals("true")){
										curField.setRequired(true);
									}
								}
								else if(curTag.equals("colWidth")){									
									if(StringUtils.isNoneBlank(str)) {
										try {
										  int w=Integer.parseInt(str);
										  if(w>0) {
											  curField.setColWidth(w);
										  }
										  
										}catch(Exception el) {
											
										}
										
									}//else =default
								}
							}else {	
								if(curTag.equals(DESC_TAG)){
									rtn.setDesc(str);	
								}
							}
						}
						
					}
			  }	
	      }catch(Exception ex){	  
	    	  throw ex;
	      }finally{
	    	  if(is!=null){
		         is.close();
	    	  }
	      }
	      
	      rtn.setImpFields(fields);
	      return rtn;
		  
	   }
	
	
	
	/**
	 * 进一步处理导入定义
	 * 取得列的位置，数据起点行
	 * @param sheet
	 * @param fields
	 */
	public static boolean  processImpDefine(Sheet sheet, ImpDefine impDefine ){
		List<ImpField> fields=impDefine.getImpFields();
		boolean rtn=false;
		//先找默认行(初始化时DefineRowNum=1),
		Row  defineRow= sheet.getRow(impDefine.getDefineRowNum());
		if(defineRow!=null) {
			rtn= getImpDefine(fields,defineRow);
		}
		if(!rtn) {
			int start=sheet.getFirstRowNum();
			int end=sheet.getLastRowNum()+1;
			for(int r=start;r<end;r++) {
				if(r!=impDefine.getDefineRowNum()) {				
					for(ImpField field:fields){ //reset...
						field.setPosition(null);
					}
					Row  cur= sheet.getRow(r);
					if(cur!=null) {
						rtn= getImpDefine(fields,cur);
						if(rtn) { //find.......
							impDefine.setDefineRowNum(r);
							break;
						}
					}
				}	
			}
		}
		
		return rtn;
	}
	
	private static boolean getImpDefine(List<ImpField> fields,Row  defineRow) {
		short start=defineRow.getFirstCellNum();
		short end=defineRow.getLastCellNum();
		Map<String,Integer>  map=new HashMap<String,Integer>();
		for(int i=start;i<end ;i++){
			Cell c=defineRow.getCell(i);
			if(c!=null){
				try {
					String t=c.getStringCellValue();
					if(t!=null){
					   map.put(t, i);
					}
				}catch(Exception ep) {
					
				}
			}	
		}
		boolean rtn=true;
		for(ImpField field:fields){
			Integer p=map.get(field.getTitle());
			if(p!=null){
				field.setPosition(p);				
			}else {
				if(field.isRequired()) {//没有必填的
				   rtn=false;
				}
			}
		}
		return rtn;
	}
	/**
	 *  取得Cell value
	 * @param cell
	 * @param f
	 * @return
	 * @throws Exception
	 */
	public static Object getCellValue(Cell cell,ImpField f) throws Exception {
		if(cell==null){
			//System.out.println(null);
			return null;
		}
		CellType celltype=cell.getCellType();
		//System.out.println(celltype);
		Object rtn=null;
		if(celltype==CellType.NUMERIC){
				DataFormatter fmt = new DataFormatter();
				String str = fmt.formatCellValue(cell);
				if(str!=null) {  //trim...
					str=str.trim();
				}
				if(f.getType().equalsIgnoreCase("String")||f.getType().equalsIgnoreCase("java.lang.String")){
					   rtn= str;
				}else {	   
					if(StringUtils.isNotBlank(str)){
						
						if(f.getType().equalsIgnoreCase("Integer")||f.getType().equalsIgnoreCase("int")||f.getType().equalsIgnoreCase("java.lang.Integer")){
						    rtn= Integer.parseInt(str);
						}else if(f.getType().equalsIgnoreCase("Short")||f.getType().equalsIgnoreCase("java.lang.Short")){
							rtn=Short.parseShort(str);
						}else if(f.getType().equalsIgnoreCase("Long")||f.getType().equalsIgnoreCase("java.lang.Long")){
							rtn= Long.parseLong(str);
						}else if(f.getType().equalsIgnoreCase("BigDecimal")||f.getType().equalsIgnoreCase("java.math.BigDecimal")){
							rtn=new BigDecimal(str);
						}else if(f.getType().equalsIgnoreCase("Double")||f.getType().equalsIgnoreCase("java.lang.Double")){
							rtn=Double.parseDouble(str);
						}else if(f.getType().equalsIgnoreCase("Float")||f.getType().equalsIgnoreCase("java.lang.Float")){
							rtn=Float.parseFloat(str);	
						}else if(f.getType().equalsIgnoreCase("Date")||f.getType().equalsIgnoreCase("java.util.Date")){
							rtn=DateTimeUtil.toDateSmart(str);//yyyy-MM-dd 
						}else if(f.getType().equalsIgnoreCase("DateTime")){
							rtn=DateTimeUtil.toDateTime(str);//yyyy-MM-dd HH:mm:ss	
						}else if(f.getType().equalsIgnoreCase("Boolean")||f.getType().equalsIgnoreCase("java.lang.Boolean")){
							if("是".equals(str)||"true".equalsIgnoreCase(str)||"yes".equalsIgnoreCase(str)) {
								rtn=true;
							}else if("否".equals(str)||"false".equalsIgnoreCase(str)||"no".equalsIgnoreCase(str)) {
								rtn=false;
							}
						}	
					}	
				}
				/* 如果使用Double d=cell.getNumericCellValue(); 会出现2.2999999999999999999 的长浮点数
				Double d=cell.getNumericCellValue();
				if(f.getType().equalsIgnoreCase("String")||f.getType().equalsIgnoreCase("java.lang.String")){
				   rtn= (new BigDecimal(d)).toString();
				}else if(f.getType().equalsIgnoreCase("Integer")||f.getType().equals("int")||f.getType().equalsIgnoreCase("java.lang.Integer")){
					rtn=d.intValue() ;
				}else if(f.getType().equalsIgnoreCase("Long")||f.getType().equalsIgnoreCase("java.lang.Long")){
					rtn= d.longValue();
				}else if(f.getType().equalsIgnoreCase("Short")||f.getType().equalsIgnoreCase("java.lang.Short")){
					rtn= d.shortValue();	
				}else if(f.getType().equalsIgnoreCase("BigDecimal")||f.getType().equalsIgnoreCase("java.math.String")){
					rtn=new BigDecimal(d);
				}else if(f.getType().equalsIgnoreCase("Double")||f.getType().equalsIgnoreCase("java.lang.Double")){
					rtn=d;
				}else if(f.getType().equalsIgnoreCase("Boolean")||f.getType().equalsIgnoreCase("java.lang.Boolean")){
					if(d.intValue()>0) {
						rtn=true;
					}else if(d.intValue()==0){
						rtn=false;
					}
				}*/ 
				
		}else if(celltype==CellType.STRING){
			String str =cell.getStringCellValue();
			if(str!=null) {  //trim...
				str=str.trim();
			}
			if(f.getType().equalsIgnoreCase("String")||f.getType().equalsIgnoreCase("java.lang.String")){
			   rtn= str;
			}else {
				if(StringUtils.isNotBlank(str)){
					if(f.getType().equalsIgnoreCase("Integer")||f.getType().equalsIgnoreCase("int")||f.getType().equalsIgnoreCase("java.lang.Integer")){
					    rtn= Integer.parseInt(str);
					}else if(f.getType().equalsIgnoreCase("Short")||f.getType().equalsIgnoreCase("java.lang.Short")){
						rtn=Short.parseShort(str);
					}else if(f.getType().equalsIgnoreCase("Long")||f.getType().equalsIgnoreCase("java.lang.Long")){
						rtn= Long.parseLong(str);
					}else if(f.getType().equalsIgnoreCase("BigDecimal")||f.getType().equalsIgnoreCase("java.math.BigDecimal")){
						rtn=new BigDecimal(str);
					}else if(f.getType().equalsIgnoreCase("Double")||f.getType().equalsIgnoreCase("java.lang.Double")){
						rtn=Double.parseDouble(str);
					}else if(f.getType().equalsIgnoreCase("Float")||f.getType().equalsIgnoreCase("java.lang.Float")){
						rtn=Float.parseFloat(str);	
					}else if(f.getType().equalsIgnoreCase("Date")||f.getType().equalsIgnoreCase("java.util.Date")){
						rtn=DateTimeUtil.toDateSmart(str);//yyyy-MM-dd 
					}else if(f.getType().equalsIgnoreCase("DateTime")){
						rtn=DateTimeUtil.toDateTime(str);//yyyy-MM-dd HH:mm:ss	
					}else if(f.getType().equalsIgnoreCase("Boolean")||f.getType().equalsIgnoreCase("java.lang.Boolean")){
						if("是".equals(str)||"true".equalsIgnoreCase(str)||"yes".equalsIgnoreCase(str)) {
							rtn=true;
						}else if("否".equals(str)||"false".equalsIgnoreCase(str)||"no".equalsIgnoreCase(str)) {
							rtn=false;
						}
					}	
				}	
			}
		}else if(celltype==CellType.BOOLEAN){
			 if(f.getType().equalsIgnoreCase("Boolean")||f.getType().equalsIgnoreCase("java.lang.Boolean")){
				rtn=cell.getBooleanCellValue();
			}
		}else if(celltype==CellType.BLANK){	
		}else {
			throw new Exception("单元格格式错误");
		}
		return rtn;
	}
	
	/**
	 *      如果不需要特别处理行数据，直接从row取得数据设置bean值 ，
	 * @param r
	 * @param fields
	 * @param bean
	 * @throws Exception
	 */
	public static void setObjectValueByRow(Row r, List<ImpField> fields,Object bean) throws ImpValidException {
		HashMap<String,String> errmap=new HashMap<String,String>();
		for(ImpField f :fields){
			if(f.getPosition()!=null){
				Cell cell=r.getCell(f.getPosition());
				if(cell==null){
					continue;
				}
				Object value=null;
				try {
                    value=getCellValue(cell,f);
				}catch(Exception ex) {
					errmap.put(f.getName(), "数据不符合规范，取值失败");
				}
				
				try {
					PropertyUtils.setProperty(bean, f.getName(),value);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
			}
		   
		}
		if(errmap.size()>0) {
			throw new ImpValidException("取值失败",errmap);
		}
	}
	
	/**
	 * 删除sheet 一行
	 * @param sheet
	 * @param rowIndex
	 */
	public static void removeRow(Sheet sheet,int rowIndex) {   
        int lastRowNum=sheet.getLastRowNum();   
        if(rowIndex>=0&&rowIndex<lastRowNum){   
            sheet.shiftRows(rowIndex+1,lastRowNum, -1);   
        }   
        if(rowIndex==lastRowNum){   
            Row removingRow=sheet.getRow(rowIndex);   
            if(removingRow!=null){   
                sheet.removeRow(removingRow);   
            }   
        }   
    }  
	
	
	/**
	 * 得到导入模板Excel
	 * @param defineXml
	 * @param appendDesc 附加说明
	 * @return
	 * @throws Exception
	 */
	public static  SXSSFWorkbook getImpTemplateExcel(String defineXmlFileName,String appendDesc) throws Exception{
		
		ImpDefine impDefine=getImpDefine(defineXmlFileName);
	    List<ImpField> fields=impDefine.getImpFields();
	    SXSSFWorkbook workbook = new SXSSFWorkbook(300);// 创建一个Excel流文件  
		Font descfont = workbook.createFont(); 
		descfont.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
		CellStyle desctstyle = workbook.createCellStyle(); 
		desctstyle.setFont(descfont);
		desctstyle.setVerticalAlignment(VerticalAlignment.CENTER);
		desctstyle.setWrapText(true);
		
	    // 设置表头字体  
		Font headfont0 = workbook.createFont(); 
	    headfont0.setFontName("黑体");  
	    headfont0.setFontHeightInPoints((short) 12);// 字体大小  
	    headfont0.setBold(true);// 加粗  
	    
	    Font headfont = workbook.createFont(); 
	    headfont.setFontName("黑体");  
	    headfont.setFontHeightInPoints((short) 12);// 字体大小  
	    headfont.setBold(true);// 加粗  
	    headfont.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
	    
	    // 表头的样式  
		CellStyle  columnHeadStyle0 = workbook.createCellStyle();  
	    columnHeadStyle0.setFont(headfont0);  
	    columnHeadStyle0.setLocked(true);
	    columnHeadStyle0.setBorderBottom(BorderStyle.THIN); 
	    columnHeadStyle0.setAlignment(HorizontalAlignment.CENTER);
	    columnHeadStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
	    columnHeadStyle0.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); // 设置单元格的边框颜色  
	    columnHeadStyle0.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
	    columnHeadStyle0.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	    columnHeadStyle0.setBorderBottom(BorderStyle.THIN);
	    columnHeadStyle0.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
	    columnHeadStyle0.setBorderLeft(BorderStyle.THIN);
	    columnHeadStyle0.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
	    columnHeadStyle0.setBorderRight(BorderStyle.THIN);
	    columnHeadStyle0.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
	    columnHeadStyle0.setBorderTop(BorderStyle.THIN);
	    columnHeadStyle0.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
	    
	    CellStyle  columnHeadStyle = workbook.createCellStyle();  
	    columnHeadStyle.setFont(headfont);  
	    columnHeadStyle.setLocked(true);
	    columnHeadStyle.setBorderBottom(BorderStyle.THIN); 
	    columnHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	    columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);
	    columnHeadStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());	   
	    //设置单元格的背景颜色	
	    columnHeadStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
	    columnHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	    columnHeadStyle.setBorderBottom(BorderStyle.THIN);
	    columnHeadStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
	    columnHeadStyle.setBorderLeft(BorderStyle.THIN);
	    columnHeadStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
	    columnHeadStyle.setBorderRight(BorderStyle.THIN);
	    columnHeadStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
	    columnHeadStyle.setBorderTop(BorderStyle.THIN);
	    columnHeadStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
	    
	    /**设置格式为文本格式*/
	    CellStyle textStyle = workbook.createCellStyle();
	    DataFormat format = workbook.createDataFormat();
	    textStyle.setDataFormat(format.getFormat("@"));
	    //列宽
	    SXSSFSheet sheet = workbook.createSheet();
	    sheet.setDefaultRowHeight((short)380);
 		for(int i=0;i<fields.size();i++){
 			ImpField tf=fields.get(i);
 	        sheet.setColumnWidth(i, tf.getColWidth()*512);
 	        //列格式
 	        if(tf.getType().equalsIgnoreCase("String")||tf.getType().equalsIgnoreCase("java.lang.String")
 	        		||tf.getType().equalsIgnoreCase("Date")||tf.getType().equalsIgnoreCase("java.util.Date")
 	        		||tf.getType().equalsIgnoreCase("DateTime")){
 	          sheet.setDefaultColumnStyle(i, textStyle);
 	         
 	       } 
 	        
 		}
 		
 		 //创建第一行,导入说明
 		 SXSSFRow row0 = sheet.createRow(0);  
	     SXSSFCell cell0 = row0.createCell(0);
	     sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
	    
	     cell0.setCellStyle(desctstyle);
	     StringBuffer buf=new StringBuffer();
	     buf.append("导入说明：\n");
	     buf.append("1.“红色”列为必填。\n");
	     
	     int n=2;
	     if(StringUtils.isNotBlank(impDefine.getDesc())){
	    	 String[] descs=impDefine.getDesc().split("\n");
	    	 for(String desc:descs){
	    		 if(StringUtils.isNotBlank(desc)) {
	    		   buf.append(n).append(".").append(desc.trim()).append("\n");
	    		   n++;
	    		 }
	    	 }
	     }
	     if(StringUtils.isNotBlank(appendDesc)){
	    	 String[] descs=appendDesc.split("\n");
	    	 for(String desc:descs){
	    		 if(StringUtils.isNotBlank(desc))
	    		 buf.append(n).append(".").append(desc).append("\n");
	    		 n++;
	    	 }
	     }
	     buf.deleteCharAt(buf.length()-1);
	     cell0.setCellValue(buf.toString());//new XSSFRichTextString("导入、导出说明：\n\r")
	      // 设置行高  
	      row0.setHeight((short)(800+(n-2)*300)); 


	      //列名行
	      SXSSFRow fieldRow=sheet.createRow(impDefine.getDefineRowNum());
	 
	      fieldRow.setHeight((short) 450);
	      for(int i=0;i<fields.size();i++){
	    	  ImpField field=fields.get(i);
	    	  SXSSFCell c = fieldRow.createCell(i);
	    	  if(field.isRequired()){
	    		  c.setCellStyle(columnHeadStyle);
	    	  }
	    	  else {	  
	    		  c.setCellStyle(columnHeadStyle0);
	    	  }
	    	  c.setCellValue(field.getTitle());
	    	  
	      }
	   
	    return workbook;
	}
	
	/**
     * 创建下拉列表选项(单元格下拉框数据小于255字节时使用)
     *
     * @param sheet    所在Sheet页面
     * @param values   下拉框的选项值
     * @param firstRow 起始行（从0开始）
     * @param lastRow  终止行（从0开始）
     * @param firstCol 起始列（从0开始）
     * @param lastCol  终止列（从0开始）
     */
    public static void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if (dataValidation instanceof HSSFDataValidation ) {
            dataValidation.setSuppressDropDownArrow(false);
        } else {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        sheet.addValidationData(dataValidation);
    }

	
	
    
   

}
